
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[EBAS_SP_RET_STUDERENDE]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[EBAS_SP_RET_STUDERENDE]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE [DBO].[EBAS_SP_RET_STUDERENDE] 
	@session varchar(255),
	@guid varchar(255),
	@cpr varchar(50),
	@fornavn varchar(255),
	@efternavn varchar(255),
	@titel varchar(255),
	@skoleId varchar(255),
	@skoleDato varchar(50),
	@okDato varchar(50),
	@bem varchar(255),
	@uddan varchar(255),
	@status varchar(255),
	@ident varchar(255)
WITH ENCRYPTION	
AS
	DECLARE
		@statusGuid uniqueidentifier,
		@identGuid uniqueidentifier,
		@brugerGuid uniqueidentifier,
		@ownBrugerGuid uniqueidentifier,
		@rettighed int

	SET NOCOUNT ON

	SET @ownBrugerGuid = (	SELECT SIKKERHED_BRUGER_GUID 
				FROM EBAS_SIKKERHED_SESSION
				WHERE GUID = @session )

	CREATE TABLE #temp ( rett int )
	INSERT INTO #temp (rett)
	EXEC EBAS.DBO.EBAS_SP_SIKKERHED_VERIFICER_SESSION @session	

	SET @rettighed = (SELECT IsNull(max(rett),0) FROM #temp)

	IF (@ownBrugerGuid = @guid AND 2 <= @rettighed) OR 8 <= @rettighed
	BEGIN
		SET @statusGuid = (SELECT GUID FROM EBAS_STATUS WHERE STATUS = @status)
		SET @identGuid = (SELECT GUID FROM EBAS_IDENTITY WHERE IDENTIFIKATION = @ident)	

		UPDATE EBAS_STUDERENDE
		SET 	CPR_NR = @cpr, FORNAVN = @fornavn, EFTERNAVN = @efternavn, TITEL = @titel,
			SKOLE_ID = @skoleId, SKOLE_DATO = @skoleDato, OK_DATO = @okDato,
			BEMAERKNING = @bem, STATUS_GUID = @statusguid, IDENTIFIKATION_GUID = @identGuid	
		WHERE GUID = @guid
	END

	IF (@ownBrugerGuid = @guid AND 1 <= @rettighed) OR 4 <= @rettighed
	BEGIN
		SET @brugerGuid = (SELECT SIKKERHED_BRUGER_GUID FROM EBAS_STUDERENDE WHERE GUID = @guid)

		IF @brugerGuid IS NOT NULL
			SELECT 	S.GUID, S.CPR_NR, S.FORNAVN, S.EFTERNAVN, S.TITEL, S.SKOLE_ID,
				S.SKOLE_DATO, S.OK_DATO, S.BEMAERKNING, S.UDDANNELSE, 
				ST.STATUS, IDEN.IDENTIFIKATION, B.BRUGERNAVN
			FROM 	EBAS_STUDERENDE S, EBAS_STATUS ST, EBAS_IDENTIFIKATION IDEN, 
				EBAS_SIKKERHED_BRUGER B
			WHERE	S.GUID = @guid
			AND	S.STATUS_GUID = ST.GUID
			AND	S.IDENTIFIKATION_GUID = IDEN.GUID
			AND	S.SIKKERHED_BRUGER_GUID = B.GUID
		ELSE
			SELECT 	S.GUID, S.CPR_NR, S.FORNAVN, S.EFTERNAVN, S.TITEL, S.SKOLE_ID,
				S.SKOLE_DATO, S.OK_DATO, S.BEMAERKNING, S.UDDANNELSE, 
				ST.STATUS, IDEN.IDENTIFIKATION, '' AS BRUGERNAVN
			FROM 	EBAS_STUDERENDE S, EBAS_STATUS ST, EBAS_IDENTIFIKATION IDEN
			WHERE	S.GUID = @guid
			AND	S.STATUS_GUID = ST.GUID
			AND	S.IDENTIFIKATION_GUID = IDEN.GUID		
	END
	ELSE
			SELECT 	S.GUID, S.CPR_NR, S.FORNAVN, S.EFTERNAVN, S.TITEL, S.SKOLE_ID,
				S.SKOLE_DATO, S.OK_DATO, S.BEMAERKNING, S.UDDANNELSE, 
				ST.STATUS, IDEN.IDENTIFIKATION, '' AS BRUGERNAVN
			FROM 	EBAS_STUDERENDE S, EBAS_STATUS ST, EBAS_IDENTIFIKATION IDEN
			WHERE	S.IDENTIFIKATION_GUID = IDEN.GUID
			AND	S.STATUS_GUID = ST.GUID
			AND	S.GUID = NULL	
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
